﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.OleDb;
using System.Configuration;
using System.Data;

/// <summary>
/// Summary description for RoomDBConnection
/// </summary>
public class RoomDBConnection
{
    OleDbConnection conn;
    OleDbCommand cmd;
    OleDbDataAdapter datatAdapter;
    OleDbDataReader dataReader;
    public String connString = "";

	public RoomDBConnection()
    {
        conn = new OleDbConnection();
        conn.ConnectionString = ConfigurationManager.ConnectionStrings["AssetDbConnection"].ConnectionString;
        //cmd = new OleDbCommand();
    }


    public Room getRoomByNo(int roomNo)
    {
        connString = "SELECT * FROM Classroom WHERE Room_no = @Room";
        cmd = conn.CreateCommand();
        cmd.CommandText = connString;

        conn.Open();

        OleDbParameter computerIDParam = new OleDbParameter();
        computerIDParam.ParameterName = "@Person";
        computerIDParam.OleDbType = OleDbType.Numeric;
        //computerIDParam.Size = 13;
        computerIDParam.Direction = ParameterDirection.Input;
        computerIDParam.Value = roomNo;

        cmd.Parameters.Add(computerIDParam);

        //myOleDbCommand.Connection.Open();
        OleDbDataReader dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

        Room room = new Room();
        while (dataReader.Read())
        {
            room.room_No = Int32.Parse(dataReader["Room_No"].ToString());
            room.room_Name = dataReader["Room_Name"].ToString();
        }

        closeObjects();

        return room;
    }


    public Room getRoomByName(String roomName)
    {
        connString = "SELECT * FROM Classroom WHERE Room_Name = @Room";
        cmd = conn.CreateCommand();
        cmd.CommandText = connString;

        conn.Open();

        OleDbParameter computerIDParam = new OleDbParameter();
        computerIDParam.ParameterName = "@Room";
        computerIDParam.OleDbType = OleDbType.VarChar;
        computerIDParam.Size = 255;
        computerIDParam.Direction = ParameterDirection.Input;
        computerIDParam.Value = roomName;

        cmd.Parameters.Add(computerIDParam);

        //myOleDbCommand.Connection.Open();
        OleDbDataReader dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

        Room room = new Room();
        while (dataReader.Read())
        {
            room.room_No = Int32.Parse(dataReader["Room_No"].ToString());
            room.room_Name = dataReader["Room_Name"].ToString();
        }

        closeObjects();

        return room;
    }


    public int update(Computer computer)
    {
        int RecordsAffected;

        OleDbCommand myOleDbCommand;
        OleDbConnection myOleDbConnection = new OleDbConnection();
        myOleDbConnection.ConnectionString = ConfigurationManager.ConnectionStrings["AssetDbConnection"].ConnectionString;


        String connStringUpdate = "UPDATE Computers SET Computer_Name = '" + computer.computer_Name + "' WHERE Computer_ID = " + computer.computer_ID + ";";

        myOleDbCommand = new OleDbCommand(connStringUpdate, myOleDbConnection);
        myOleDbConnection.Open();

        RecordsAffected = myOleDbCommand.ExecuteNonQuery();
        myOleDbConnection.Close();

        return RecordsAffected;
    }

    public void delete(Computer computer)
    {
        OleDbCommand myOleDbCommand;
        OleDbConnection myOleDbConnection = new OleDbConnection();
        myOleDbConnection.ConnectionString = ConfigurationManager.ConnectionStrings["AssetDbConnection"].ConnectionString;


        String connString = "DELETE FROM Computers WHERE Computers_ID = " + computer.computer_ID + ";";

        myOleDbCommand = new OleDbCommand(connString, myOleDbConnection);
        myOleDbConnection.Open();

        myOleDbConnection.Close();
    }

    public void closeObjects()
    {
        if (conn.State.ToString().Equals("Open"))
        {
            conn.Close();
        }
        if (cmd.Connection.State.ToString().Equals("Open"))
        {
            cmd.Connection.Close();
        }
    }



}